﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Text;

/// <summary>
///YJLR 的摘要说明
/// </summary>
public class YJLR
{
  public YJLR()
  {
    //
    //TODO: 在此处添加构造函数逻辑
    //
  }

  public static string GetSKHID(string sID)
  {
    string KHID = "";
    string sSql = "select * from SJLR_yjlr where yjid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      KHID = dr["khid"].ToString();
    }
    dr.Close();
    return KHID;
  }

  public static SqlDataReader GetYJLR(string sID)
  {
    string sSql = "select *,convert(varchar,yjlrrq,23) rq from sjlr_yjlr where yjid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    return dr;
  }

  //  public static DataSet GetDS()
  //  {
  //    StringBuilder sbSql = new StringBuilder();
  //    sbSql.Append(@"select  ROW_NUMBER() OVER (ORDER BY c.khid desc) AS xh,a.YJ,c.*
  //from (select khid,sum(yj) yj from sjlr_yjlr group by KHID) a,jcxx_khxx c 
  //where a.khid=c.khid ");
  //    //sbSql.Append("order by RQ desc");
  //    SqlParameter[] cmdParams = new SqlParameter[1]{ new SqlParameter("@0", SqlDbType.VarChar, 10)};
  //    cmdParams[0].Value = SysManage.Get_User;
  //    DataSet ds = DbHelperSQL.Query(sbSql.ToString(), cmdParams);
  //    return ds;
  //  }
  public static DataSet GetDS(string date1, string date2, string user)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append(@"select  ROW_NUMBER() OVER (ORDER BY a.yjid desc) AS xh,a.*,convert(varchar,yjlrrq,23) RQ,b.*,c.*
from sjlr_yjlr a,sys_user b,jcxx_khxx c where a.dlm=b.dlm and a.khid=c.khid  and yjlrrq>=@0 and yjlrrq<=cast(@1 as datetime) ");
    //sbSql.Append("order by RQ desc");
    //if (!string.IsNullOrEmpty(user))
    //  sbSql.Append("and a.dlm=@2");
    SqlParameter[] cmdParams = new SqlParameter[3]{ new SqlParameter("@0", SqlDbType.VarChar, 10),
      new SqlParameter("@1", SqlDbType.VarChar, 10), new SqlParameter("@2", SqlDbType.VarChar, 10)};
    cmdParams[0].Value = date1;
    cmdParams[1].Value = date2;
    cmdParams[2].Value = user;
    DataSet ds = DbHelperSQL.Query(sbSql.ToString(), cmdParams);
    return ds;
  }
  public static DataSet GetDS_KH(string khid, string user)
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append(@"select  ROW_NUMBER() OVER (ORDER BY a.yjid desc) AS xh,a.*,convert(varchar,yjlrrq,23) RQ,b.*,c.*
from sjlr_yjlr a,sys_user b,jcxx_khxx c 
where a.dlm=b.dlm and a.khid=c.khid and a.khid=@0 ");
    if (!string.IsNullOrEmpty(user))
      sbSql.Append("and a.dlm=@1");
    sbSql.Append("order by a.yjlrrq desc");
    SqlParameter[] cmdParams = new SqlParameter[2]{ new SqlParameter("@0", SqlDbType.VarChar, 10),
      new SqlParameter("@1", SqlDbType.VarChar, 10)};
    cmdParams[0].Value = khid;
    cmdParams[1].Value = user;
    DataSet ds = DbHelperSQL.Query(sbSql.ToString(), cmdParams);
    return ds;
  }

  public static string GetYJ(string sKHID)
  {
    string KHID = "0";
    string sSql = "select isnull(sum(yj),0) yj from SJLR_yjlr where khid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      dr.Read();
      KHID = dr["yj"].ToString();
    }
    dr.Close();
    return KHID;
  }
  public static bool ExistHQID(string sKHID, string sRQ)
  {
    bool flag = false;
    string sSql = "select yjid as num from sjlr_yjlr where yjlrrq>@0 and khid=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sRQ;
    cmdParams[1].Value = sKHID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }
}
